Imports System.Activator
Imports System
Imports System.Collections.Generic

''' <summary>
''' Create a SQL statement to populate a DOCollection.
''' </summary>
''' <remarks>
''' The SQL builder class is used to compose SQL statements without having to 
''' use actual any SQL script. An understanding of SQL is always a good thing
''' to have. You will notice that the BizBlox Builder tool will create shadow 
''' the columns enumerated type when creating a data object. The enumerated 
''' type created is used to reference the column when creating any SQL 
''' statement.
'''
''' Foreign tables are referenced by passing an instance of the corresponding 
''' DataObject as the foreign table.
''' </remarks>
''' <history>
''' 	[cinj]	6/07/2004	Created
''' </history>
Public Class SQLBuilder

    Private m_select As Boolean = False
    Private m_selectStr As String = ""

    Private m_DistinctStr As String = ""

    Private m_topStr As String = ""
    Private m_topCount As Integer = 0

    Private m_from As Boolean = False
    Private m_fromStr As String = ""
    Private m_joinStr As String = ""


    Private m_where As Boolean = False
    Private m_whereStr As String = ""

    Private m_groupby As Boolean = False
    Private m_groupbyStr As String = ""

    Private m_having As Boolean = False
    Private m_havingStr As String = ""

    Private mLOGIC_ORderby As Boolean = False
    Private mLOGIC_ORderbyStr As String = ""

    Private m_parenthesisCount As Integer = 0
    Private m_primaryTable As String = ""

    Private m_startRowNumber As Integer = -1
    Private m_endRowNumber As Integer = -1
    Private m_selectedColumns As New List(Of DOSelection)

    Public Sub New(ByVal tableName As String)
        m_primaryTable = tableName
    End Sub


#Region "Render"



    ''' <summary>
    ''' Render the SQLBulder object as a SELECT statement.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks>
    ''' Deprecated. Use RenderSelect instead.
    ''' </remarks>
    ''' <history>
    ''' 	[cinj]	6/07/2005	Created
    ''' </history>

    <Obsolete("This method should never be called. It has been superceded by the RenderSelect() method.", True)> _
    Public Function Render() As String
        Return RenderSelect()
    End Function


    ''' <summary>
    ''' Render the SQLBulder object as a SELECT statement.
    ''' </summary>
    ''' <returns>Rendered SQL statement</returns>
    ''' <remarks>
    ''' Renders a SQL statement of the form:
    ''' <code>SELECT select_list FROM table_source ...</code>
    ''' </remarks>
    ''' <history>
    ''' 	[cinj]	6/07/2005	Created
    ''' </history>
    Public Function RenderSelect() As String
        If Me.m_startRowNumber >= 0 Then
            Return Me.RenderSelectRange(Me.m_startRowNumber, Me.m_endRowNumber)
        Else
            addFrom()
            If m_parenthesisCount > 0 Then
                Throw New InvalidOperationException("Error! SqlBuilder cannot render invalid SQL Statement (parenthesis grouping is unbalanced) " & m_selectStr & " " & m_fromStr & " " & m_joinStr & " " & m_whereStr & " " & m_groupbyStr & " " & m_havingStr & " " & mLOGIC_ORderbyStr)
            End If
            Return "SELECT " & m_DistinctStr & " " & m_topStr & " " & m_selectStr & " " & m_fromStr & " " & m_joinStr & " " & m_whereStr & " " & m_groupbyStr & " " & m_havingStr & " " & mLOGIC_ORderbyStr
        End If
    End Function

    Private Function RenderSelectRange(ByVal startIndex As Integer, ByVal endIndex As Integer) As String
        addFrom()
        If m_parenthesisCount > 0 Then
            Throw New InvalidOperationException("Error! SqlBuilder cannot render invalid SQL Statement (parenthesis grouping is unbalanced) " & m_selectStr & " " & m_fromStr & " " & m_joinStr & " " & m_whereStr & " " & m_groupbyStr & " " & m_havingStr & " " & mLOGIC_ORderbyStr)
        End If
        Return "WITH results AS (SELECT " & m_DistinctStr & " " & m_topStr & " " & m_selectStr & ", ROW_NUMBER() OVER (" & mLOGIC_ORderbyStr & ") AS rownumber " _
        & m_fromStr & " " & m_joinStr & " " & m_whereStr & " " & m_groupbyStr & " " & m_havingStr & ") SELECT * from results WHERE RowNumber Between " & startIndex & " AND " & endIndex
    End Function



    ''' <summary>
    ''' Render the SQLBuilder object as a DELETE statement.
    ''' </summary>
    ''' <returns>Rendered SQL statement</returns>
    ''' <remarks>
    ''' Renders a SQL statement of the form:
    ''' <code>DELETE FROM table_source ...</code>
    ''' </remarks>
    ''' <history>
    ''' 	[cinj]	6/07/2005	Created
    ''' </history>

    Public Function RenderDelete() As String
        addFrom()
        If m_parenthesisCount > 0 Then
            Throw New InvalidOperationException("Error! SqlBuilder cannot render invalid SQL Statement (parenthesis grouping is unbalanced) " & m_joinStr & " " & m_whereStr)
        End If
        Return "DELETE " & m_fromStr & " " & m_joinStr & " " & m_whereStr
    End Function


    ''' <summary>
    ''' Render a SELECT COUNT(*) query.
    ''' </summary>
    ''' <returns>Rendered SQL Statement</returns>
    ''' <remarks>
    ''' Renders a SQL statement of the form:
    ''' <code>SELECT COUNT(*) FROM table_source ...</code>
    ''' <para>Use this method if you wish to count the number of rows returned by the query,
    ''' without requiring access to the data.</para>
    ''' </remarks>
    ''' <history>
    ''' 	[cinj]	9/06/2006	Created
    ''' </history>
    Public Function RenderCount(ByVal label As String, ByVal tableName As String, ByVal columnName As String) As String
        addFrom()
        If m_parenthesisCount > 0 Then
            Throw New InvalidOperationException("Error! SqlBuilder cannot render invalid SQL Statement (parenthesis grouping is unbalanced) " & m_joinStr & " " & m_whereStr)
        End If
        If Me.m_DistinctStr <> "" Then
            'This allows us to do a DISTINCT Count if it is explicitly specified, e.g. COUNT(DISTINCT Firstname) FROM Customer
            Return "SELECT COUNT(" & m_DistinctStr & " " & tableName & "." & columnName & ") as " & label & " " & m_fromStr & " " & m_joinStr & " " & m_whereStr
        Else
            'If DISTINCT is not explicitly specified, just do a COUNT(*), to avoid problems with doing a COUNT(uniqueidentifier) in earlier SQL Server versions
            Return "SELECT COUNT(" & tableName & "." & columnName & ") as " & label & " " & m_fromStr & " " & m_joinStr & " " & m_whereStr
        End If
    End Function



    ''' <summary>
    ''' Render a SELECT SUM(Table.ColumnName) query.
    ''' </summary>
    ''' <returns>Rendered SQL Statement</returns>
    ''' <remarks>
    ''' Renders a SQL statement of the form:
    ''' <code>SELECT SUM(Table.ColumnName) FROM table_source ...</code>
    ''' <para>Use this method if you wish to find the sum of the values in a single column,
    ''' without requiring access to the data.</para>
    ''' </remarks>
    Public Function RenderSum(ByVal table As DataObject, ByVal column As [Enum], ByVal label As String, ByVal columnType As DBColumnType) As String
        addFrom()
        If m_parenthesisCount > 0 Then
            Throw New InvalidOperationException("Error! SqlBuilder cannot render invalid SQL Statement (parenthesis grouping is unbalanced) " & m_joinStr & " " & m_whereStr)
        End If
        Dim selectStr As String = table.TableName & "." & column.ToString
        Select Case columnType
            Case DBColumnType.float
                Return "SELECT SUM(" & selectStr & ") as " & label & " " & m_fromStr & " " & m_joinStr & " " & m_whereStr
            Case DBColumnType.text, DBColumnType.ntext
                Return "SELECT SUM(CAST(CAST(" & selectStr & " AS varchar(50)) AS float)) as " & label & " " & m_fromStr & " " & m_joinStr & " " & m_whereStr
            Case Else
                Return "SELECT SUM(CAST(" & selectStr & " AS float)) as " & label & " " & m_fromStr & " " & m_joinStr & " " & m_whereStr
        End Select
        If columnType <> DBColumnType.float Then
        Else
        End If
    End Function



    ''' <summary>
    ''' Render a SELECT AVG(Table.ColumnName) query.
    ''' </summary>
    ''' <returns>Rendered SQL Statement</returns>
    ''' <remarks>
    ''' Renders a SQL statement of the form:
    ''' <code>SELECT AVG(Table.ColumnName) FROM table_source ...</code>
    ''' <para>Use this method if you wish to find the average of the values in a single column,
    ''' without requiring access to the data.</para>
    ''' </remarks>

    Public Function RenderAverage(ByVal table As DataObject, ByVal column As [Enum], ByVal label As String, ByVal columnType As DBColumnType) As String
        addFrom()
        If m_parenthesisCount > 0 Then
            Throw New InvalidOperationException("Error! SqlBuilder cannot render invalid SQL Statement (parenthesis grouping is unbalanced) " & m_joinStr & " " & m_whereStr)
        End If
        Dim selectStr As String = table.TableName & "." & column.ToString
        Select Case columnType
            Case DBColumnType.float
                Return "SELECT AVG(" & selectStr & ") as " & label & " " & m_fromStr & " " & m_joinStr & " " & m_whereStr
            Case DBColumnType.text, DBColumnType.ntext
                Return "SELECT AVG(CAST(CAST(" & selectStr & " AS varchar(50)) AS float)) as " & label & " " & m_fromStr & " " & m_joinStr & " " & m_whereStr
            Case Else
                Return "SELECT AVG(CAST(" & selectStr & " AS float)) as " & label & " " & m_fromStr & " " & m_joinStr & " " & m_whereStr
        End Select
    End Function



#End Region


#Region "Select"

    ''' <summary>
    ''' Reset the SELECT statement to nothing.
    ''' </summary>
    ''' <remarks>
    ''' The SELECT statament is used based on the context of the invocation from the DOCollection and therefore it needs 
    ''' to be able to be cleared. The SELECT should not be set by the developer code in DOCollections or any other use within
    ''' </remarks>
    Public Sub ClearSelect()
        m_select = False
        m_selectStr = ""
        Me.m_selectedColumns.Clear()
    End Sub

    Friend Sub SetSelect(ByVal tableName As String, ByVal columnName As String)
        ClearSelect()
        addSelectImpl(tableName, columnName)
        Me.m_selectedColumns.Add(New DOSelection(tableName, columnName))
    End Sub

    Friend Sub SetSelect(ByVal selections() As DOSelection)
        ClearSelect()
        For i As Integer = 0 To selections.Length - 1
            addSelect(selections(i))
        Next
    End Sub

    ''' -----------------------------------------------------------------------------
    ''' <overloads>
    ''' Add columns to the SELECT list. 
    ''' </overloads>
    ''' <summary>
    ''' Add columns from the primary table to the SELECT list. 
    ''' </summary>
    ''' <param name="columns"></param>
    ''' <remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as SQLBuilder
    ''' mySQl.addSelect(Student.Columns.Name)
    ''' </code>
    ''' </example>
    ''' </remarks>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Sub addSelect(ByVal ParamArray columns() As [Enum])
        For i As Integer = 0 To columns.Length - 1
            addSelectImpl(Me.m_primaryTable, columns(i).ToString())
            Me.m_selectedColumns.Add(New DOSelection(Me.m_primaryTable, columns(i).ToString()))
        Next
    End Sub

    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Add columns from a foreign table to the SELECT list. 
    ''' </summary>
    ''' <param name="columns"></param>
    ''' <remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as SQLBuilder
    ''' mySQl.addSelect(New Food, Food.Columns.Name, Food.Columns.Price)
    ''' </code>
    ''' </example>
    ''' </remarks>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Sub addSelect(ByVal foreignTable As DataObject, ByVal ParamArray columns() As [Enum])
        For i As Integer = 0 To columns.Length - 1
            addSelectImpl(foreignTable.TableName, columns(i).ToString())
            Me.m_selectedColumns.Add(New DOSelection(foreignTable, columns(i)))
        Next
    End Sub

    Public Sub addSelectAll(ByVal table As DataObject)
        addSelectImpl(table.TableName, "*")
        Me.m_selectedColumns.Add(New DOSelection(table))
    End Sub


    Public Sub addSelectAll()
        addSelectImpl(Me.TableName, "*")
        Me.m_selectedColumns.Add(New DOSelection(Me.TableName))
    End Sub

    Public Sub addSelect(ByVal selection As DOSelection)
        If m_select = False Then
            m_select = True
            m_selectStr = ""
        Else
            m_selectStr = m_selectStr & ","
        End If
        m_selectStr &= selection.Render(True)
        Me.m_selectedColumns.Add(selection)
    End Sub

    Private Sub addSelectImpl(ByVal tableName As String, ByVal column As String)
        If m_select = False Then
            m_select = True
            m_selectStr = ""
        Else
            m_selectStr = m_selectStr & ","
        End If
        m_selectStr = m_selectStr & IIf(tableName <> "", tableName & ".", "") & column
    End Sub

    Public ReadOnly Property Selections() As DOSelection()
        Get
            Return Me.m_selectedColumns.ToArray()
        End Get
    End Property

#End Region


#Region "From"

    Friend Sub addFrom()
        If m_from = True Then
            Return
        End If
        m_from = True
        m_fromStr = "FROM " & m_primaryTable
    End Sub

#End Region

#Region "Top"

    ''' <summary>
    ''' Add a TOP predicate to the SELECT.
    ''' </summary>
    ''' <param name="val"></param>
    ''' <remarks>
    ''' Adding a TOP statement limits the number of rows returned by the query.
    ''' </remarks>
    ''' <history>
    ''' 	[cinj]	9/06/2006	Created
    ''' </history>

    Public Sub setTop(ByVal val As Integer)
        m_topCount = val
        m_topStr = "TOP " & val
    End Sub


    ''' <summary>
    ''' Clear the TOP predicate from the SELECT.
    ''' </summary>
    ''' <remarks>
    ''' Clears a TOP predicate previously set using setTop().
    ''' </remarks>
    ''' <history>
    ''' 	[cinj]	9/06/2006	Created
    ''' </history>

    Public Sub clearTop()
        m_topCount = 0
        m_topStr = ""
    End Sub


    ''' <summary>
    ''' Gets or Sets the value of the TOP predicate for the SELECT statement.
    ''' </summary>
    ''' <value></value>
    ''' <remarks>
    ''' Adding a TOP statement limits the number of rows returned by the query.
    ''' </remarks>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Property TopCount() As Integer
        Get
            Return m_topCount
        End Get
        Set(ByVal Value As Integer)
            m_topCount = Value
            m_topStr = "TOP " & Value
        End Set
    End Property
#End Region

#Region "Distinct"

    ''' <summary>
    ''' Add a DISTINCT predicate to the SELECT statement.
    ''' </summary>
    ''' <remarks>
    ''' <para>The DISTINCT predicate is used to omit duplicate values in a field.</para>
    ''' <para>Calling this method results in a SELECT statement of the form:
    ''' <code>
    ''' SELECT DISTINCT select_list FROM table_source
    ''' </code>
    ''' </para>
    ''' </remarks>
    ''' <history>
    ''' 	[tres]	10/08/2006	Created
    ''' </history>

    Public Sub addDistinct()
        m_DistinctStr = "DISTINCT "
    End Sub


    ''' <summary>
    ''' Clear the DISTINCT predicate from the SELECT statement.
    ''' </summary>
    ''' <remarks>
    ''' Clears a DISTINCT predicate previously set using AddDistinct().
    ''' </remarks>
    ''' <history>
    ''' 	[tres]	10/08/2006	Created
    ''' </history>

    Public Sub clearDistinct()
        m_DistinctStr = ""
    End Sub

#End Region

#Region "Inner Join"



    ''' <overloads>
    ''' Adds an INNER JOIN between two tables to the SELECT statement
    ''' </overloads>
    ''' <summary>
    ''' Adds an INNER JOIN from the primary table to the specified foreign table, joining on the specified columns.
    ''' </summary>
    ''' <param name="localKeyColumn">The column in the primary table to join from.</param>
    ''' <param name="joinTable">The foreign table to join to.</param>
    ''' <param name="joinFKeyColumn">The column in the foreign table to join to.</param>
    ''' <remarks>
    ''' Adding a JOIN allows querying across the primary table and a foreign table.
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQl.addInnerJoin(Student.Columns.StudentID, New LunchOrder, LunchOrder.Columns.StudentID)
    ''' mySQL.addWhere(New LunchOrder, LunchOrder.Columns.Quantity, 1)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addInnerJoin(ByVal localKeyColumn As [Enum], ByVal joinTable As DataObject, ByVal joinFKeyColumn As [Enum])
        If Not Me.ContainsJoin(Me.m_primaryTable, localKeyColumn, joinTable.TableName, joinFKeyColumn) Then
            m_joinStr = m_joinStr & "INNER JOIN " & joinTable.TableName & " ON " & m_primaryTable & "." & localKeyColumn.ToString() & "=" & joinTable.TableName & "." & joinFKeyColumn.ToString() & " "
        End If
    End Sub


    ''' <summary>
    ''' Adds an INNER JOIN using the specified columns in the two specified tables.
    ''' </summary>
    ''' <param name="existingJoinTable">A table already existing in the query - either the Primary table, or a table added in a previous JOIN.</param>
    ''' <param name="existingKeyColumn">The column from the existing table to join on.</param>
    ''' <param name="newJoinTable">The new table to join to.</param>
    ''' <param name="newKeyColumn">The column in the new table to join on.</param>
    ''' <remarks>
    ''' This method allows a sequential INNER JOIN across more than two tables.
    ''' </remarks>
    ''' <example>
    ''' This example uses a basic initial INNER JOIN to join the Student and LunchOrder tables.
    ''' It then calls the current overloaded INNER JOIN method to join the LunchOrder and Food tables.
    ''' It then adds WHERE clauses to search for students aged less than 15, with lunch orders
    ''' for food costing more than $10.00.
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addInnerJoin(Student.Columns.StudentID, New LunchOrder, LunchOrder.Columns.StudentID)
    ''' mySQL.addInnerJoin(New LunchOrder, LunchOrder.Columns.FoodID, New Food, Food.Columns.FoodID)
    ''' mySQL.addWhereLT(Student.Columns.Age, "15")
    ''' mySQL.addWhereGT(New Food, Food.Columns.Price, "10.00")
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addInnerJoin(ByVal existingJoinTable As DataObject, ByVal existingKeyColumn As [Enum], ByVal newJoinTable As DataObject, ByVal newKeyColumn As [Enum])
        If Not Me.ContainsJoin(existingJoinTable, existingKeyColumn, newJoinTable, newKeyColumn) Then
            m_joinStr = m_joinStr & "INNER JOIN " & newJoinTable.TableName & " ON " & existingJoinTable.TableName & "." & existingKeyColumn.ToString() & "=" & newJoinTable.TableName & "." & newKeyColumn.ToString() & " "
        End If
    End Sub

    Private Function ContainsJoin(ByVal existingTable As DataObject, ByVal existingColumn As [Enum], ByVal newTable As DataObject, ByVal newColumn As [Enum]) As Boolean
        Return Me.ContainsJoin(existingTable.TableName, existingColumn, newTable.TableName, newColumn)
    End Function

    Private Function ContainsJoin(ByVal existingTableName As String, ByVal existingColumn As [Enum], ByVal newTableName As String, ByVal newColumn As [Enum]) As Boolean
        Dim joinSqlMinusSpaces As String = "ON" & existingTableName & "." & existingColumn.ToString & "=" & newTableName & "." & newColumn.ToString
        If Me.m_joinStr.ToLower.Replace(" ", "").IndexOf(joinSqlMinusSpaces.ToLower) >= 0 Then
            Return True
        Else
            Return False
        End If
    End Function



#End Region

#Region "Outer Join"


    ''' <overloads>
    ''' Adds a LEFT OUTER JOIN between two tables to the SELECT statement
    ''' </overloads>
    ''' <summary>
    ''' Adds a LEFT OUTER JOIN from the primary table to the specified foreign table, joining on the specified columns.
    ''' </summary>
    ''' <param name="localKeyColumn">The column in the primary table to join from.</param>
    ''' <param name="joinTable">The foreign table to join to.</param>
    ''' <param name="joinFKeyColumn">The column in the foreign table to join to.</param>
    ''' <remarks>
    ''' Adding a JOIN allows querying across the primary table and a foreign table.
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQl.addLeftOuterJoin(Student.Columns.StudentID, New LunchOrder, LunchOrder.Columns.StudentID)
    ''' mySQL.addWhere(New LunchOrder, LunchOrder.Columns.Quantity, 1)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addLeftOuterJoin(ByVal localKeyColumn As [Enum], ByVal joinTable As DataObject, ByVal joinFKeyColumn As [Enum])
        If Not Me.ContainsJoin(Me.m_primaryTable, localKeyColumn, joinTable.TableName, joinFKeyColumn) Then
            m_joinStr = m_joinStr & "LEFT OUTER JOIN " & joinTable.TableName & " ON " & m_primaryTable & "." & localKeyColumn.ToString() & "=" & joinTable.TableName & "." & joinFKeyColumn.ToString() & " "
        End If
    End Sub


    ''' <summary>
    ''' Adds a LEFT OUTER JOIN using the specified columns in the two specified tables.
    ''' </summary>
    ''' <param name="existingJoinTable">A table already existing in the query - either the Primary table, or a table added in a previous JOIN.</param>
    ''' <param name="existingKeyColumn">The column from the existing table to join on.</param>
    ''' <param name="newJoinTable">The new table to join to.</param>
    ''' <param name="newKeyColumn">The column in the new table to join on.</param>
    ''' <remarks>
    ''' This method allows a sequential LEFT OUTER JOIN across more than two tables.
    ''' </remarks>
    ''' <example>
    ''' This example uses a basic initial LEFT OUTER JOIN to join the Student and LunchOrder tables.
    ''' It then calls the current overloaded LEFT OUTER JOIN method to join the LunchOrder and Food tables.
    ''' It then adds WHERE clauses to search for students aged less than 15, with lunch orders
    ''' for food costing more than $10.00.
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addLeftOuterJoin(Student.Columns.StudentID, New LunchOrder, LunchOrder.Columns.StudentID)
    ''' mySQL.addLeftOuterJoin(New LunchOrder, LunchOrder.Columns.FoodID, New Food, Food.Columns.FoodID)
    ''' mySQL.addWhereLT(Student.Columns.Age, "15")
    ''' mySQL.addWhereGT(New Food, Food.Columns.Price, "10.00")
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addLeftOuterJoin(ByVal existingJoinTable As DataObject, ByVal existingKeyColumn As [Enum], ByVal newJoinTable As DataObject, ByVal newKeyColumn As [Enum])
        If Not Me.ContainsJoin(existingJoinTable, existingKeyColumn, newJoinTable, newKeyColumn) Then
            m_joinStr = m_joinStr & "LEFT OUTER JOIN " & newJoinTable.TableName & " ON " & existingJoinTable.TableName & "." & existingKeyColumn.ToString() & "=" & newJoinTable.TableName & "." & newKeyColumn.ToString() & " "
        End If
    End Sub


    ''' <overloads>
    ''' Adds a RIGHT OUTER JOIN between two tables to the SELECT statement
    ''' </overloads>
    ''' <summary>
    ''' Adds a RIGHT OUTER JOIN from the primary table to the specified foreign table, joining on the specified columns.
    ''' </summary>
    ''' <param name="localKeyColumn">The column in the primary table to join from.</param>
    ''' <param name="joinTable">The foreign table to join to.</param>
    ''' <param name="joinFKeyColumn">The column in the foreign table to join to.</param>
    ''' <remarks>
    ''' Adding a JOIN allows querying across the primary table and a foreign table.
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQl.addRightOuterJoin(Student.Columns.StudentID, New LunchOrder, LunchOrder.Columns.StudentID)
    ''' mySQL.addWhere(New LunchOrder, LunchOrder.Columns.Quantity, 1)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addRightOuterJoin(ByVal localKeyColumn As [Enum], ByVal joinTable As DataObject, ByVal joinFKeyColumn As [Enum])
        If Not Me.ContainsJoin(Me.m_primaryTable, localKeyColumn, joinTable.TableName, joinFKeyColumn) Then
            m_joinStr = m_joinStr & "RIGHT OUTER JOIN " & joinTable.TableName & " ON " & m_primaryTable & "." & localKeyColumn.ToString() & "=" & joinTable.TableName & "." & joinFKeyColumn.ToString() & " "
        End If
    End Sub


    ''' <summary>
    ''' Adds a RIGHT OUTER JOIN using the specified columns in the two specified tables.
    ''' </summary>
    ''' <param name="existingJoinTable">A table already existing in the query - either the Primary table, or a table added in a previous JOIN.</param>
    ''' <param name="existingKeyColumn">The column from the existing table to join on.</param>
    ''' <param name="newJoinTable">The new table to join to.</param>
    ''' <param name="newKeyColumn">The column in the new table to join on.</param>
    ''' <remarks>
    ''' This method allows a sequential RIGHT OUTER JOIN across more than two tables.
    ''' </remarks>
    ''' <example>
    ''' This example uses a basic initial RIGHT OUTER JOIN to join the Student and LunchOrder tables.
    ''' It then calls the current overloaded RIGHT OUTER JOIN method to join the LunchOrder and Food tables.
    ''' It then adds WHERE clauses to search for students aged less than 15, with lunch orders
    ''' for food costing more than $10.00.
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addRightOuterJoin(Student.Columns.StudentID, New LunchOrder, LunchOrder.Columns.StudentID)
    ''' mySQL.addRightOuterJoin(New LunchOrder, LunchOrder.Columns.FoodID, New Food, Food.Columns.FoodID)
    ''' mySQL.addWhereLT(Student.Columns.Age, "15")
    ''' mySQL.addWhereGT(New Food, Food.Columns.Price, "10.00")
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addRightOuterJoin(ByVal existingJoinTable As DataObject, ByVal existingKeyColumn As [Enum], ByVal newJoinTable As DataObject, ByVal newKeyColumn As [Enum])
        If Not Me.ContainsJoin(existingJoinTable, existingKeyColumn, newJoinTable, newKeyColumn) Then
            m_joinStr = m_joinStr & "RIGHT OUTER JOIN " & newJoinTable.TableName & " ON " & existingJoinTable.TableName & "." & existingKeyColumn.ToString() & "=" & newJoinTable.TableName & "." & newKeyColumn.ToString() & " "
        End If
    End Sub


    ''' <overloads>
    ''' Adds a FULL OUTER JOIN between two tables to the SELECT statement
    ''' </overloads>
    ''' <summary>
    ''' Adds a FULL OUTER JOIN from the primary table to the specified foreign table, joining on the specified columns.
    ''' </summary>
    ''' <param name="localKeyColumn">The column in the primary table to join from.</param>
    ''' <param name="joinTable">The foreign table to join to.</param>
    ''' <param name="joinFKeyColumn">The column in the foreign table to join to.</param>
    ''' <remarks>
    ''' Adding a JOIN allows querying across the primary table and a foreign table.
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQl.addFullOuterJoin(Student.Columns.StudentID, New LunchOrder, LunchOrder.Columns.StudentID)
    ''' mySQL.addWhere(New LunchOrder, LunchOrder.Columns.Quantity, 1)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addFullOuterJoin(ByVal localKeyColumn As [Enum], ByVal joinTable As DataObject, ByVal joinFKeyColumn As [Enum])
        If Not Me.ContainsJoin(Me.m_primaryTable, localKeyColumn, joinTable.TableName, joinFKeyColumn) Then
            m_joinStr = m_joinStr & "FULL OUTER JOIN " & joinTable.TableName & " ON " & m_primaryTable & "." & localKeyColumn.ToString() & "=" & joinTable.TableName & "." & joinFKeyColumn.ToString() & " "
        End If
    End Sub


    ''' <summary>
    ''' Adds a FULL OUTER JOIN using the specified columns in the two specified tables.
    ''' </summary>
    ''' <param name="existingJoinTable">A table already existing in the query - either the Primary table, or a table added in a previous JOIN.</param>
    ''' <param name="existingKeyColumn">The column from the existing table to join on.</param>
    ''' <param name="newJoinTable">The new table to join to.</param>
    ''' <param name="newKeyColumn">The column in the new table to join on.</param>
    ''' <remarks>
    ''' This method allows a sequential FULL OUTER JOIN across more than two tables.
    ''' </remarks>
    ''' <example>
    ''' This example uses a basic initial FULL OUTER JOIN to join the Student and LunchOrder tables.
    ''' It then calls the current overloaded FULL OUTER JOIN method to join the LunchOrder and Food tables.
    ''' It then adds WHERE clauses to search for students aged less than 15, with lunch orders
    ''' for food costing more than $10.00.
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addFullOuterJoin(Student.Columns.StudentID, New LunchOrder, LunchOrder.Columns.StudentID)
    ''' mySQL.addFullOuterJoin(New LunchOrder, LunchOrder.Columns.FoodID, New Food, Food.Columns.FoodID)
    ''' mySQL.addWhereLT(Student.Columns.Age, "15")
    ''' mySQL.addWhereGT(New Food, Food.Columns.Price, "10.00")
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addFullOuterJoin(ByVal existingJoinTable As DataObject, ByVal existingKeyColumn As [Enum], ByVal newJoinTable As DataObject, ByVal newKeyColumn As [Enum])
        If Not Me.ContainsJoin(existingJoinTable, existingKeyColumn, newJoinTable, newKeyColumn) Then
            m_joinStr = m_joinStr & "FULL OUTER JOIN " & newJoinTable.TableName & " ON " & existingJoinTable.TableName & "." & existingKeyColumn.ToString() & "=" & newJoinTable.TableName & "." & newKeyColumn.ToString() & " "
        End If
    End Sub

#End Region

#Region "Where"

#Region "Local Table"


    ''' <overloads>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' where the value of the specified column matches the specified String.
    ''' </overloads>
    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the primary table where the value of the specified column matches the specified String.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="value"></param>
    ''' <remarks>
    ''' This method will also search for numeric values, such as Integers and Doubles.
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addWhere(Student.Columns.Name, "John Citizen")
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addWhere(ByVal column As [Enum], ByVal value As String)
        doWhere(True, False, m_primaryTable, column, value)
    End Sub



    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the primary table where the value of the specified column matches the specified Guid.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="value"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim studentId as New Guid(Me.txStudentId.Text)
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addWhere(Student.Columns.StudentID, studentId)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addWhere(ByVal column As [Enum], ByVal value As Guid)
        doWhere(True, False, m_primaryTable, column, value.ToString())
    End Sub


    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the primary table where the value of the specified column matches the specified Integer value.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="value"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Customer")
    ''' mySQL.addWhere(Customer.Columns.Age,21)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addWhere(ByVal column As [Enum], ByVal value As Integer)
        doWhere(m_primaryTable, column, value)
    End Sub


    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the primary table where the value of the specified column matches the specified Boolean value.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="value"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Customer")
    ''' mySQL.addWhere(Customer.Columns.Active,True)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addWhere(ByVal column As [Enum], ByVal value As Boolean)
        doWhere(True, False, m_primaryTable, column, IIf(value, "1", "0"))
    End Sub


    ''' <overloads>
    ''' Adds a condition to the WHERE clause, to search for rows
    '''  where the value of the specified column is LIKE the specified String.
    ''' </overloads>
    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the primary table where the value of the specified column is LIKE the specified String.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="value"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addWhereLike(Student.Columns.Name, "%Citizen%")
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addWhereLike(ByVal column As [Enum], ByVal value As String)
        doWhere(True, True, m_primaryTable, column, value)
    End Sub


    ''' <overloads>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' where the value of the specified column is LIKE the specified String, using a case-insensitive comparison.
    ''' </overloads>
    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the primary table where the value of the specified column is LIKE the specified String, using a case-insensitive comparison.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="value"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addWhereLike(Student.Columns.Name, "%Citizen%")
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addWhereLikeNoCase(ByVal column As [Enum], ByVal value As String)
        doWhere(False, True, m_primaryTable, column, value)
    End Sub


    ''' <overloads>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' where the value of the specified column matches the specified String, using a case-insensitive comparison.
    ''' </overloads>
    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the primary table where the value of the specified column matches the specified String, using a case-insensitive comparison.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="value"></param>
    ''' <remarks>
    ''' This method will also search for numeric values, such as Integers and Doubles.
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addWhere(Student.Columns.Name, "John Citizen")
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addWhereNoCase(ByVal column As [Enum], ByVal value As String)
        doWhere(False, False, m_primaryTable, column, value)
    End Sub



    ''' <overloads>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' where the value of specified column is NULL.
    ''' </overloads>
    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the primary table where the value of specified column is NULL.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.addWhereNull(LunchOrder.Columns.FoodID)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub AddWhereNull(ByVal column As [Enum])
        doWhere(False, False, m_primaryTable, column, Nothing)
    End Sub


#Region "Where In"


    ''' <overloads>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' where the value of the specified column is IN the range of values specified in the statement parameter.
    ''' </overloads>
    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the primary table where the value of the specified column is IN the range of values specified in the statement parameter.
    ''' </summary>
    ''' <param name="column">The column in the primary table to search on.</param>
    ''' <param name="statement">Either a list of comma-separated values, or a SQL statement that will return a list of values. 
    ''' In both cases it must match the data type of the column being searched upon.</param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <para>This example demonstrates two ways of using the addWhereIn method:</para>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addWhereIn(Student.Column.Age,"10,11,12")
    ''' mySQL.addOr()
    ''' mySQL.addWhereIn(Student.Column.StudentID, "SELECT StudentID FROM LunchOrder WHERE Quantity > 1")
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub AddWhereIn(ByVal column As [Enum], ByVal statement As String)
        initWhere()
        m_whereStr = m_whereStr & "(" & m_primaryTable & "." & column.ToString() & " IN (" & statement & "))"
    End Sub

    Public Sub AddWhereIn(ByVal column As [Enum], ByVal ids() As Guid)
        Dim statement As String = ""
        If ids Is Nothing OrElse ids.Length = 0 Then
            Return
        End If
        For i As Integer = 0 To ids.Length - 1
            statement &= "'" & ids(i).ToString & "'"
            If Not i = ids.Length - 1 Then
                statement &= ","
            End If
        Next
        initWhere()
        m_whereStr = m_whereStr & "(" & m_primaryTable & "." & column.ToString() & " IN (" & statement & "))"
    End Sub

    Public Sub AddWhereIn(ByVal column As [Enum], ByVal ids() As String)
        Dim statement As String = ""
        If ids Is Nothing OrElse ids.Length = 0 Then
            Return
        End If
        For i As Integer = 0 To ids.Length - 1
            statement &= "'" & ids(i) & "'"
            If Not i = ids.Length - 1 Then
                statement &= ","
            End If
        Next
        initWhere()
        m_whereStr = m_whereStr & "(" & m_primaryTable & "." & column.ToString() & " IN (" & statement & "))"
    End Sub

#End Region


#Region "Where Exists"


    ''' <overloads>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' where the value of the specified column EXISTS in the statement parameter.
    ''' </overloads>
    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the primary table where the value of the specified column EXISTS in the statement parameter.
    ''' </summary>
    ''' <param name="statement">A SQL statement that will return a list of values.</param>
    ''' <remarks>
    ''' This is particularly used when there are NULL values in the nested table, which causes a 
    ''' WHERE NOT IN to fail. 
    ''' </remarks>
    ''' <history>
    ''' 	[tres]	15/07/2009	Created
    ''' </history>

    Public Sub AddWhereExists(ByVal statement As String)
        initWhere()
        m_whereStr = m_whereStr & "(EXISTS (" & statement & "))"
    End Sub


#End Region

#End Region

#Region "Foreign Table"



    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the specified table where the value of the specified column matches the specified String.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="column"></param>
    ''' <param name="value"></param>
    ''' <remarks>    ''' 
    ''' This method will also search for numeric values, such as Integers and Doubles.
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySql.addInnerJoin(LunchOrder.Columns.StudentID, New Student, Student.StudentID)
    ''' mySQL.addWhere(New Student, Student.Columns.Name, "John Citizen")
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addWhere(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal value As String)
        doWhere(True, False, foreignTable.TableName, column, value)
    End Sub


    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the specified table where the value of the specified column matches the specified Guid.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="column"></param>
    ''' <param name="value"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim studentId as New Guid(Me.txStudentId.Text)
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySql.addInnerJoin(LunchOrder.Columns.StudentID, New Student, Student.StudentID)
    ''' mySQL.addWhere(New Student, Student.Columns.StudentID, studentId)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addWhere(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal value As Guid)
        doWhere(True, False, foreignTable.TableName, column, value.ToString())
    End Sub



    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the specified table where the value of the specified column matches the specified Boolean value.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="column"></param>
    ''' <param name="value"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySql.addInnerJoin(LunchOrder.Columns.StudentID, New Student, Student.StudentID)
    ''' mySQL.addWhere(New Student, Student.Columns.AllowLunchOrders, True)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addWhere(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal value As Boolean)
        doWhere(True, False, foreignTable.TableName, column, IIf(value, "1", "0"))
    End Sub


    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the specified table where the value of the specified column is LIKE the specified String, using a case-insensitive comparison.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="column"></param>
    ''' <param name="value"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySql.addInnerJoin(LunchOrder.Columns.StudentID, New Student, Student.StudentID)
    ''' mySQL.addWhereLike(New Student, Student.Columns.Name, "%Citizen%")
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addWhereNoCase(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal value As String)
        doWhere(False, False, foreignTable.TableName, column, value)
    End Sub


    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the specified table where the value of the specified column is LIKE the specified String, using a case-insensitive comparison.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="column"></param>
    ''' <param name="value"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySql.addInnerJoin(LunchOrder.Columns.StudentID, New Student, Student.StudentID)
    ''' mySQL.addWhereLike(New Student, Student.Columns.Name, "%Citizen%")
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addWhereLike(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal value As String)
        doWhere(True, True, foreignTable.TableName, column, value)
    End Sub


    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the specified table where the value of the specified column matches the specified String, using a case-insensitive comparison.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="column"></param>
    ''' <param name="value"></param>
    ''' <remarks>
    ''' This method will also search for numeric values, such as Integers and Doubles.
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySql.addInnerJoin(LunchOrder.Columns.StudentID, New Student, Student.StudentID)
    ''' mySQL.addWhere(New Student, Student.Columns.Name, "John Citizen")
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addWhereLikeNoCase(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal value As String)
        doWhere(False, True, foreignTable.TableName, column, value)
    End Sub


    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the specified table where the value of the specified column is IN the range of values specified in the statement parameter.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="column">The column in the primary table to search on.</param>
    ''' <param name="statement">Either a list of comma-separated values, or a SQL statement that will return a list of values. 
    ''' In both cases it must match the data type of the column being searched upon.</param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <para>This example demonstrates two ways of using the addWhereIn method:</para>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySql.addInnerJoin(LunchOrder.Columns.StudentID, New Student, Student.StudentID)
    ''' mySQL.addWhereIn(New Student, Student.Column.Age,"10,11,12")
    ''' mySQL.addOr()
    ''' mySQL.addWhereIn(New Student, Student.Column.StudentID, "SELECT StudentID FROM LunchOrder WHERE Quantity > 1")
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub AddWhereIn(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal statement As String)
        initWhere()
        m_whereStr = m_whereStr & "(" & foreignTable.TableName & "." & column.ToString() & " IN (" & statement & "))"
    End Sub

    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the specified table where the value of the specified column is IN the range of values specified in the ids parameter.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="column"></param>
    ''' <param name="ids"></param>
    ''' <remarks></remarks>
    Public Sub AddWhereIn(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal ids() As Guid)
        Dim statement As String = ""
        If ids Is Nothing OrElse ids.Length = 0 Then
            Return
        End If
        For i As Integer = 0 To ids.Length - 1
            statement &= "'" & ids(i).ToString & "'"
            If Not i = ids.Length - 1 Then
                statement &= ","
            End If
        Next
        initWhere()
        m_whereStr = m_whereStr & "(" & foreignTable.TableName & "." & column.ToString() & " IN (" & statement & "))"
    End Sub

    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the specified table where the value of the specified column is IN the range of values specified in the ids parameter.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="column"></param>
    ''' <param name="ids"></param>
    ''' <remarks></remarks>
    Public Sub AddWhereIn(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal ids() As String)
        Dim statement As String = ""
        If ids Is Nothing OrElse ids.Length = 0 Then
            Return
        End If
        For i As Integer = 0 To ids.Length - 1
            statement &= "'" & ids(i) & "'"
            If Not i = ids.Length - 1 Then
                statement &= ","
            End If
        Next
        initWhere()
        m_whereStr = m_whereStr & "(" & foreignTable.TableName & "." & column.ToString() & " IN (" & statement & "))"
    End Sub



    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the specified table where the value of specified column is NULL.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="column"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySql.addInnerJoin(Student.Columns.StudentID, New LunchOrder, LunchOrder.StudentID)
    ''' mySQL.addWhereNull(New LunchOrder, LunchOrder.Columns.FoodID)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub AddWhereNull(ByVal foreignTable As DataObject, ByVal column As [Enum])
        doWhere(False, False, foreignTable.TableName, column, Nothing)
    End Sub

#End Region


#Region "WHERE comparing 2 columns"

    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows in the query
    ''' where the specified column in Table 1 has the same value as the specified column in Table 2
    ''' </summary>
    ''' <param name="table1"></param>
    ''' <param name="column1"></param>
    ''' <param name="table2"></param>
    ''' <param name="column2"></param>
    ''' <remarks></remarks>
    Public Sub AddWhere(ByVal table1 As DataObject, ByVal column1 As [Enum], ByVal table2 As DataObject, ByVal column2 As [Enum])
        initWhere()
        m_whereStr = m_whereStr & "(" & table1.TableName & "." & column1.ToString & " = " & table2.TableName & "." & column2.ToString & ")"
    End Sub

    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows in the query
    ''' where the specified column in Table 1 has a greater value than the specified column in Table 2
    ''' </summary>
    Public Sub AddWhereGT(ByVal table1 As DataObject, ByVal column1 As [Enum], ByVal table2 As DataObject, ByVal column2 As [Enum])
        initWhere()
        m_whereStr = m_whereStr & "(" & table1.TableName & "." & column1.ToString & " > " & table2.TableName & "." & column2.ToString & ")"
    End Sub

    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows in the query
    ''' where the specified column in Table 1 has a greater than or equal value than the specified column in Table 2
    ''' </summary>
    Public Sub AddWhereGTE(ByVal table1 As DataObject, ByVal column1 As [Enum], ByVal table2 As DataObject, ByVal column2 As [Enum])
        initWhere()
        m_whereStr = m_whereStr & "(" & table1.TableName & "." & column1.ToString & " >= " & table2.TableName & "." & column2.ToString & ")"
    End Sub

    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows in the query
    ''' where the specified column in Table 1 has a lesser value than the specified column in Table 2
    ''' </summary>
    Public Sub AddWhereLT(ByVal table1 As DataObject, ByVal column1 As [Enum], ByVal table2 As DataObject, ByVal column2 As [Enum])
        initWhere()
        m_whereStr = m_whereStr & "(" & table1.TableName & "." & column1.ToString & " < " & table2.TableName & "." & column2.ToString & ")"
    End Sub

    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows in the query
    ''' where the specified column in Table 1 has a less than or equal to value than the specified column in Table 2
    ''' </summary>
    Public Sub AddWhereLTE(ByVal table1 As DataObject, ByVal column1 As [Enum], ByVal table2 As DataObject, ByVal column2 As [Enum])
        initWhere()
        m_whereStr = m_whereStr & "(" & table1.TableName & "." & column1.ToString & " <= " & table2.TableName & "." & column2.ToString & ")"
    End Sub

#End Region

#Region "DATES"
    Private Sub doWhereDate(ByVal column As String, ByVal dateVal As Date, ByVal comparison As String)
        initWhere()
        Dim dateStr As String = DOFormatter.DateToString(dateVal)
        m_whereStr = m_whereStr & "(" & column & " " & comparison & " '" & dateStr & "')"
    End Sub



    ''' <overloads>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' where the value of the specified column is greater than the given date.
    ''' </overloads>
    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the primary table where the value of the specified column is greater than the given date.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="dateVal"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.addWhereDateGT(LunchOrder.Columns.dt_OrderDate,DateTime.UtcNow)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub AddWhereDateGT(ByVal column As [Enum], ByVal dateval As DateTime)
        doWhereDate(m_primaryTable & "." & column.ToString(), dateval, ">")
    End Sub


    ''' <overloads>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' where the value of the specified column is greater than or equal to the given date.
    ''' </overloads>
    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the primary table where the value of the specified column is greater than 
    ''' or equal to the given date.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="dateVal"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.addWhereDateGTE(LunchOrder.Columns.dt_OrderDate,DateTime.UtcNow)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub AddWhereDateGTE(ByVal column As [Enum], ByVal dateval As DateTime)
        doWhereDate(m_primaryTable & "." & column.ToString(), dateval, ">=")
    End Sub


    ''' <overloads>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' where the value of the specified column is less than the given date.
    ''' </overloads>
    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the primary table where the value of the specified column is 
    ''' less than the given date.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="dateVal"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.addWhereDateLT(LunchOrder.Columns.dt_OrderDate,DateTime.UtcNow)
    ''' </code>
    ''' </example> 
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub AddWhereDateLT(ByVal column As [Enum], ByVal dateval As DateTime)
        doWhereDate(m_primaryTable & "." & column.ToString(), dateval, "<")
    End Sub


    ''' <overloads>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' where the value of the specified column is less than or equal to the given date.
    ''' </overloads>
    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the primary table where the value of the specified column is 
    ''' less than or equal to the given date.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="dateVal"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.addWhereDateLTE(LunchOrder.Columns.dt_OrderDate,DateTime.UtcNow)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub AddWhereDateLTE(ByVal column As [Enum], ByVal dateval As DateTime)
        doWhereDate(m_primaryTable & "." & column.ToString(), dateval, "<=")
    End Sub


    ''' <overloads>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' where the value of the specified column is equal to the given date.
    ''' </overloads>
    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the primary table where the value of the specified column is 
    ''' equal to the given date.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="dateVal"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.addWhereDateEQ(LunchOrder.Columns.dt_OrderDate,DateTime.UtcNow)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub AddWhereDateEQ(ByVal column As [Enum], ByVal dateval As DateTime)
        doWhereDate(m_primaryTable & "." & column.ToString(), dateval, "=")
    End Sub


    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the specified table where the value of the specified column is 
    ''' greater than the given date.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="dateVal"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addInnerJoin(Student.Columns.StudentID, New LunchOrder, LunchOrder.Columns.StudentID)
    ''' mySQL.addWhereDateGT(New LunchOrder, LunchOrder.Columns.dt_OrderDate,DateTime.UtcNow)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub AddWhereDateGT(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal dateval As DateTime)
        doWhereDate(foreignTable.TableName & "." & column.ToString(), dateval, ">")
    End Sub


    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the specified table where the value of the specified column is 
    ''' greater than or equal to the given date.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="dateVal"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addInnerJoin(Student.Columns.StudentID, New LunchOrder, LunchOrder.Columns.StudentID)
    ''' mySQL.addWhereDateGTE(New LunchOrder, LunchOrder.Columns.dt_OrderDate,DateTime.UtcNow)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub AddWhereDateGTE(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal dateval As DateTime)
        doWhereDate(foreignTable.TableName & "." & column.ToString(), dateval, ">=")
    End Sub



    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the specified table where the value of the specified column is 
    ''' less than the given date.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="dateVal"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addInnerJoin(Student.Columns.StudentID, New LunchOrder, LunchOrder.Columns.StudentID)
    ''' mySQL.addWhereDateLT(New LunchOrder, LunchOrder.Columns.dt_OrderDate,DateTime.UtcNow)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub AddWhereDateLT(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal dateval As DateTime)
        doWhereDate(foreignTable.TableName & "." & column.ToString(), dateval, "<")
    End Sub



    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the specified table where the value of the specified column is 
    ''' less than or equal to the given date.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="dateVal"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addInnerJoin(Student.Columns.StudentID, New LunchOrder, LunchOrder.Columns.StudentID)
    ''' mySQL.addWhereDateLTE(New LunchOrder, LunchOrder.Columns.dt_OrderDate,DateTime.UtcNow)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub AddWhereDateLTE(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal dateval As DateTime)
        doWhereDate(foreignTable.TableName & "." & column.ToString(), dateval, "<=")
    End Sub


    ''' <summary>
    ''' Adds a condition to the WHERE clause, to search for rows
    ''' in the specified table where the value of the specified column is 
    ''' equal to the given date.
    ''' </summary>
    ''' <param name="column"></param>
    ''' <param name="dateVal"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addInnerJoin(Student.Columns.StudentID, New LunchOrder, LunchOrder.Columns.StudentID)
    ''' mySQL.addWhereDateEQ(New LunchOrder, LunchOrder.Columns.dt_OrderDate,DateTime.UtcNow)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub AddWhereDateEQ(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal dateval As DateTime)
        doWhereDate(foreignTable.TableName & "." & column.ToString(), dateval, "=")
    End Sub

#End Region


#Region "Comparison Operators"

    Private Sub doWhereComparison(ByVal tableName As String, ByVal column As [Enum], ByVal comparison As String, ByVal value As String)
        initWhere()
        m_whereStr = m_whereStr & "(" & tableName & "." & column.ToString() & " " & comparison & " '" & value & "')"
    End Sub


    ''' <summary>
    ''' Generates a WHERE clause using the &gt; (Greater Than) comparison operator.
    ''' </summary>
    ''' <param name="column">The column to be compared.</param>
    ''' <param name="value">The value to be compared against.</param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Food")
    ''' mySQL.addWhereGT(Food.Columns.Price, "10.00")
    ''' </code>
    ''' </example> 
    ''' <history>
    ''' 	[tres]	24/08/2006	Created
    ''' </history>

    Public Sub AddWhereGT(ByVal column As [Enum], ByVal value As String)
        doWhereComparison(m_primaryTable, column, ">", value)
    End Sub


    ''' <summary>
    ''' Generates a WHERE clause using the &gt;= (Greater Than or Equal) comparison operator.
    ''' </summary>
    ''' <param name="column">The column to be compared.</param>
    ''' <param name="value">The value to be compared against.</param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Food")
    ''' mySQL.addWhereGTE(Food.Columns.Price, "10.00")
    ''' </code>
    ''' </example> 
    ''' <history>
    ''' 	[tres]	24/08/2006	Created
    ''' </history>

    Public Sub AddWhereGTE(ByVal column As [Enum], ByVal value As String)
        doWhereComparison(m_primaryTable, column, ">=", value)
    End Sub


    ''' <summary>
    ''' Generates a WHERE clause using the &lt; (Less Than) comparison operator.
    ''' </summary>
    ''' <param name="column">The column to be compared.</param>
    ''' <param name="value">The value to be compared against.</param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Food")
    ''' mySQL.addWhereLT(Food.Columns.Price, "10.00")
    ''' </code>
    ''' </example> 
    ''' <history>
    ''' 	[tres]	24/08/2006	Created
    ''' </history>

    Public Sub AddWhereLT(ByVal column As [Enum], ByVal value As String)
        doWhereComparison(m_primaryTable, column, "<", value)
    End Sub


    ''' <summary>
    ''' Generates a WHERE clause using the &lt;= (Less Than or Equal) comparison operator.
    ''' </summary>
    ''' <param name="column">The column to be compared.</param>
    ''' <param name="value">The value to be compared against.</param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Food")
    ''' mySQL.addWhereLTE(Food.Columns.Price, "10.00")
    ''' </code>
    ''' </example> 
    ''' <history>
    ''' 	[tres]	24/08/2006	Created
    ''' </history>

    Public Sub AddWhereLTE(ByVal column As [Enum], ByVal value As String)
        doWhereComparison(m_primaryTable, column, "<=", value)
    End Sub


    ''' <summary>
    ''' Generates a WHERE clause using the &gt; (Greater Than) comparison operator.
    ''' </summary>
    ''' <param name="foreignTable">The table possessing the column to be compared.</param>
    ''' <param name="column">The column to be compared.</param>
    ''' <param name="value">The value to be compared against.</param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.addInnerJoin(LunchOrder.Columns.FoodID, New Food, Food.Columns.FoodID)
    ''' mySQL.addWhereGT(New Food, Food.Columns.Price, "10.00")
    ''' </code>
    ''' </example> 
    ''' <history>
    ''' 	[tres]	24/08/2006	Created
    ''' </history>

    Public Sub AddWhereGT(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal value As String)
        doWhereComparison(foreignTable.TableName, column, ">", value)
    End Sub


    ''' <summary>
    ''' Generates a WHERE clause using the &gt;= (Greater Than or Equal) comparison operator.
    ''' </summary>
    ''' <param name="foreignTable">The table possessing the column to be compared.</param>
    ''' <param name="column">The column to be compared.</param>
    ''' <param name="value">The value to be compared against.</param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.addInnerJoin(LunchOrder.Columns.FoodID, New Food, Food.Columns.FoodID)
    ''' mySQL.addWhereGTE(New Food, Food.Columns.Price, "10.00")
    ''' </code>
    ''' </example> 
    ''' <history>
    ''' 	[tres]	24/08/2006	Created
    ''' </history>

    Public Sub AddWhereGTE(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal value As String)
        doWhereComparison(foreignTable.TableName, column, ">=", value)
    End Sub


    ''' <summary>
    ''' Generates a WHERE clause using the &lt; (Less Than) comparison operator.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="column">The column to be compared.</param>
    ''' <param name="value">The value to be compared against.</param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.addInnerJoin(LunchOrder.Columns.FoodID, New Food, Food.Columns.FoodID)
    ''' mySQL.addWhereLT(New Food, Food.Columns.Price, "10.00")
    ''' </code>
    ''' </example> 
    ''' <history>
    ''' 	[tres]	24/08/2006	Created
    ''' </history>

    Public Sub AddWhereLT(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal value As String)
        doWhereComparison(foreignTable.TableName, column, "<", value)
    End Sub


    ''' <summary>
    ''' Generates a WHERE clause using the &lt;= (Less Than Or Equal) comparison operator.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="column">The column to be compared.</param>
    ''' <param name="value">The value to be compared against.</param>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.addInnerJoin(LunchOrder.Columns.FoodID, New Food, Food.Columns.FoodID)
    ''' mySQL.addWhereLTE(New Food, Food.Columns.Price, "10.00")
    ''' </code>
    ''' </example> 
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[tres]	24/08/2006	Created
    ''' </history>

    Public Sub AddWhereLTE(ByVal foreignTable As DataObject, ByVal column As [Enum], ByVal value As String)
        doWhereComparison(foreignTable.TableName, column, "<=", value)
    End Sub


#End Region

    Private Sub initWhere()
        If m_where = False Then
            m_where = True
            m_whereStr = "WHERE "
        End If
    End Sub

    'generate a where clause from an array of parameters (ie: column=value)
    'provide unqualified column names as it will use the foreign table name
    Private Sub doWhere(ByVal caseSensitive As Boolean, ByVal doLike As Boolean, ByVal tableName As String, ByVal column As [Enum], ByVal value As String)
        Dim started As Boolean = False
        Dim beginCI As String = IIf(caseSensitive, "", " UPPER(")
        Dim endCI As String = IIf(caseSensitive, "", ") ")

        'ignore malformed where clause

        initWhere()
        If value Is Nothing Then
            m_whereStr = m_whereStr & tableName & "." & column.ToString() & " IS NULL "
        ElseIf doLike Then
            m_whereStr = m_whereStr & beginCI & tableName & "." & column.ToString() & endCI & " LIKE " & beginCI & "'" & Replace(value, "'", "''") & "'" & endCI
        Else
            m_whereStr = m_whereStr & beginCI & tableName & "." & column.ToString() & endCI & " = " & beginCI & "'" & Replace(value, "'", "''") & "'" & endCI
        End If
        m_where = True
    End Sub

    'generate a where clause from an array of parameters (ie: column=value)
    'provide unqualified column names as it will use the foreign table name
    Private Sub doWhere(ByVal tableName As String, ByVal column As [Enum], ByVal value As Integer)
        initWhere()
        If value = Nothing Then
            m_whereStr = m_whereStr & tableName & "." & column.ToString() & " IS NULL "
        Else
            m_whereStr = m_whereStr & tableName & "." & column.ToString() & " = " & value & " "
        End If
        m_where = True
    End Sub

#End Region

#Region "Boolean"

    ''' <summary>
    ''' Opens a parenthesized NOT operator in the WHERE clause.
    ''' </summary>
    ''' <remarks>
    ''' <para>The openNot() call must be followed by a call to closeNot(), once the conditions have been added inside.</para>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.openNot()
    ''' mySQL.addWhere(LunchOrder.Columns.Quantity, 1)
    ''' mySQL.closeNot() 
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub openNot()
        openParen()
        m_whereStr = m_whereStr & "NOT"
        openParen()
    End Sub


    ''' <summary>
    ''' Closes a parenthesized NOT operator in the WHERE clause.
    ''' </summary>
    ''' <remarks>
    ''' The closeNot() call must be preceded by a call to openNot().
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.openNot()
    ''' mySQL.addWhere(LunchOrder.Columns.Quantity, 1)
    ''' mySQL.closeNot() 
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub closeNot()
        closeParen()
        closeParen()
    End Sub


    ''' <summary>
    ''' Adds an AND Boolean operator to the WHERE clause.
    ''' </summary>
    ''' <remarks>
    ''' Used to link multiple conditions in the WHERE clause.
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addWhere(Student.Columns.Name,"John Citizen")
    ''' mySQL.addAnd()
    ''' mySQL.addWhere(Student.Columns.Age,15)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addAnd()
        If m_where = True Then
            m_whereStr = m_whereStr & " AND "
        End If
    End Sub


    ''' <summary>
    ''' Adds an OR Boolean operator to the WHERE clause.
    ''' </summary>
    ''' <remarks>
    ''' Used to link multiple conditions in the WHERE clause.
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addWhere(Student.Columns.Name,"John Citizen")
    ''' mySQL.addOr()
    ''' mySQL.addWhere(Student.Columns.Age,15)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addOr()
        If m_where = True Then
            m_whereStr = m_whereStr & " OR "
        End If
    End Sub


    ''' <summary>
    ''' Opens a pair of parentheses in the WHERE clause.
    ''' </summary>
    ''' <remarks>
    ''' The openParen() call must be followed by a call to closeParen(), once the conditions have been added inside the parentheses.
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addWhere(Student.Columns.Name,"John Citizen")
    ''' mySQL.addAnd()
    ''' mySQL.openParen()
    ''' mySQL.addWhere(Student.Columns.Age,15)
    ''' mySQL.addOr()
    ''' mySQL.addWhere(Student.Columns.Age,17)
    ''' mySQL.closeParen()
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub openParen()
        If m_where = True Then
            m_whereStr = m_whereStr & "("
        Else
            m_whereStr = "WHERE ("
            m_where = True
        End If
        m_parenthesisCount = m_parenthesisCount + 1
    End Sub

    ''' <summary>
    ''' Closes a pair of parentheses in the WHERE clause.
    ''' </summary>
    ''' <remarks>
    ''' The closeParen() call must be preceded by a call to openParen().
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addWhere(Student.Columns.Name,"John Citizen")
    ''' mySQL.addAnd()
    ''' mySQL.openParen()
    ''' mySQL.addWhere(Student.Columns.Age,15)
    ''' mySQL.addOr()
    ''' mySQL.addWhere(Student.Columns.Age,17)
    ''' mySQL.closeParen()
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub closeParen()
        If m_where = True Then
            m_whereStr = m_whereStr & ")"
            m_parenthesisCount = m_parenthesisCount - 1
        End If
    End Sub
#End Region

#Region "Search"


    ''' <overloads>
    ''' Add a SearchBuilder to the WHERE clause.
    ''' </overloads>
    ''' <summary>
    ''' Add a SearchBuilder to the WHERE clause, using the specified column in the primary table.
    ''' </summary>
    ''' <param name="columnName"></param>
    ''' <param name="sb"></param>
    ''' <remarks>
    ''' Adding a SearchBuilder allows for advanced string comparisons. It can be used
    ''' as a more powerful alternative to the addWhere(), addWhereLike() and related methods
    ''' when comparing String values.
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim searchTerm as String=Me.txSearchTerm.Text
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' Dim sb As New SearchBuilder()
    ''' sb.ParseNaturalLanguage(searchTerm)
    ''' mySQL.addSearch(Student.Columns.Name, sb)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addSearch(ByVal columnName As [Enum], ByVal sb As SearchBuilder)
        Dim str As String = sb.Render(m_primaryTable, columnName.ToString())
        If str.Length > 1 Then
            initWhere()
            m_whereStr = m_whereStr & str
        End If
    End Sub



    ''' <summary>
    ''' Add a SearchBuilder to the WHERE clause, using the specified column in the specified table.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="sb"></param>
    ''' <remarks>
    ''' Adding a SearchBuilder allows for advanced string comparisons. It can be used
    ''' as a more powerful alternative to the addWhere(), addWhereLike() and related methods
    ''' when comparing String values.
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim searchTerm as String=Me.txSearchTerm.Text
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.addInnerJoin(LunchOrder.Columns.StudentID, New Student, Student.Columns.StudentID)
    ''' Dim sb As New SearchBuilder()
    ''' sb.ParseNaturalLanguage(searchTerm)
    ''' mySQL.addSearch(New Student, Student.Columns.Name, sb)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addSearch(ByVal foreignTable As DataObject, ByVal foreignColumnName As [Enum], ByVal sb As SearchBuilder)
        Dim str As String = sb.Render(foreignTable.TableName, foreignColumnName.ToString())
        If str.Length > 1 Then
            initWhere()
            m_whereStr = m_whereStr & str
        End If
    End Sub


    ''' <overloads>
    ''' Searches a series of concatenated columns using a SearchBuilder.
    ''' </overloads>
    ''' <summary>
    ''' Searches a series of concatenated columns in the primary table using a SearchBuilder.
    ''' </summary>
    ''' <example>
    ''' <para>The most common usage for this is when searching a table where there
    ''' is a Firstname column and a Lastname column, and you wish to search
    ''' for a person's full name.</para>
    ''' <para>For example, to search such a Customer table for the customer 
    ''' named 'John Citizen', you could use:</para>  
    ''' <code>
    ''' Dim mySql as New SqlBuilder("Customer")
    ''' Dim searchTerm As String = "all words John Citizen"
    ''' Dim sb As New SearchBuilder(SearchTerm)
    ''' mySql.addSearchConcatenatedColumns(sb, " ", New Customer(), Customer.Columns.Firstname, Customer.Columns.Lastname)
    ''' </code>
    ''' </example>
    ''' <param name="sb">The SearchBuilder that contains the details of the term to be searched for.</param>
    ''' <param name="columnDelimiter">The term used to concatenate the multiple columns.</param>
    ''' <param name="columns">The series of columns that are to be concatenated together using the columnDelimiter.</param>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[tres]	24/08/2006	Created
    ''' </history>

    Public Sub addSearchConcatenatedColumns(ByVal sb As SearchBuilder, ByVal columnDelimiter As String, ByVal ParamArray columns() As [Enum])
        Dim str As String = sb.Render(columnDelimiter, m_primaryTable, columns)
        If str.Length > 1 Then
            initWhere()
            m_whereStr = m_whereStr & str
        End If
    End Sub


    ''' <summary>
    ''' Searches a series of concatenated columns in the specified table using a SearchBuilder.
    ''' </summary>
    ''' <example>
    ''' <para>The most common usage for this is when searching a table where there
    ''' is a Firstname column and a Lastname column, and you wish to search
    ''' for a person's full name.</para>
    ''' <para>For example, to search such a Customer table for the customer 
    ''' named 'John Citizen', you could use:</para>  
    ''' <code>
    ''' Dim mySql as New SqlBuilder("Customer")
    ''' Dim searchTerm As String = "all words John Citizen"
    ''' Dim sb As New SearchBuilder(SearchTerm)
    ''' mySql.addSearchConcatenatedColumns(sb, " ", New Customer(), Customer.Columns.Firstname, Customer.Columns.Lastname)
    ''' </code>
    ''' </example>
    ''' <param name="sb">The SearchBuilder that contains the details of the term to be searched for.</param>
    ''' <param name="columnDelimiter">The term used to concatenate the multiple columns.</param>
    ''' <param name="foreignTable">The table on which to search.</param>
    ''' <param name="columns">The series of columns that are to be concatenated together using the columnDelimiter.</param>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[tres]	24/08/2006	Created
    ''' </history>

    Public Sub addSearchConcatenatedColumns(ByVal sb As SearchBuilder, ByVal columnDelimiter As String, ByVal foreignTable As DataObject, ByVal ParamArray columns() As [Enum])
        Dim str As String = sb.Render(columnDelimiter, foreignTable.TableName, columns)
        If str.Length > 1 Then
            initWhere()
            m_whereStr = m_whereStr & str
        End If
    End Sub

#End Region

#Region "Sums and Averages"







#End Region


#Region "Group By"

    Public Sub addGroupBy(ByVal column As [Enum])
        addGroupByImpl(m_primaryTable, column)
    End Sub

    Public Sub addGroupBy(ByVal foreignTable As DataObject, ByVal column As [Enum])
        addGroupByImpl(foreignTable.TableName, column)
    End Sub

    Private Sub addGroupByImpl(ByVal foreignTableName As String, ByVal column As [Enum])
        If m_groupby = True Then
            m_groupbyStr &= ", "
        Else
            m_groupbyStr = "GROUP BY "
        End If
        m_groupby = True
        m_groupbyStr &= foreignTableName & "." & column.ToString()
    End Sub

    Public Sub addHaving(ByVal selection As DOSelection, ByVal op As DOOperator, ByVal compareToValue As Double)
        If m_having = True Then
            m_havingStr &= ", "
        Else
            m_havingStr = "HAVING "
        End If
        m_having = True
        m_havingStr &= selection.Render(False) & " "
        m_havingStr &= Me.RenderOperator(op) & " " & compareToValue.ToString()
    End Sub

    Public Sub addHaving(ByVal selection As DOSelection, ByVal op As DOOperator, ByVal compareToSelection As DOSelection)
        If m_having = True Then
            m_havingStr &= ", "
        Else
            m_havingStr = "HAVING "
        End If
        m_having = True
        m_havingStr &= selection.Render(False) & " "
        m_havingStr &= Me.RenderOperator(op) & " " & selection.Render(False)
    End Sub

    '''' <summary>
    '''' Adds any plain-text SQL statement after the HAVING keyword.
    '''' Should only be used when existing methods do not cater for needs.
    '''' </summary>
    '''' <param name="sql"></param>
    '''' <remarks></remarks>
    'Public Sub addHaving(ByVal sql As String)
    '    If m_having = True Then
    '        m_havingStr = ", "
    '    Else
    '        m_havingStr = "HAVING "
    '    End If
    '    m_having = True
    '    m_havingStr = sql
    'End Sub

#End Region


#Region "Order By"


    ''' <summary>
    ''' Add an ORDER BY (ascending sort) to the SELECT clause, using the specified columns in the primary table.
    ''' </summary>
    ''' <param name="columns"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addWhereGT(Student.Columns.Age,10)
    ''' mySQL.addOrderBy(Student.Columns.Age, Student.Columns Name)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addOrderBy(ByVal ParamArray columns() As [Enum])
        doOrderBy(m_primaryTable, False, columns)
    End Sub


    ''' <overloads>
    ''' Add an ORDER BY DESC (descending sort) to the SELECT clause.
    ''' </overloads>
    ''' <summary>
    ''' Add an ORDER BY DESC (descending sort) to the SELECT clause, using the specified columns in the primary table.
    ''' </summary>
    ''' <param name="columns"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addWhereGT(Student.Columns.Age,10)
    ''' mySQL.addOrderByDesc(Student.Columns.Age)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addOrderByDesc(ByVal ParamArray columns() As [Enum])
        doOrderBy(m_primaryTable, True, columns)
    End Sub



    ''' <overloads>
    ''' Adds either an ORDER BY (ascending sort) or ORDER BY DESC (descending sort) to the SELECT clause.
    ''' </overloads>
    ''' <summary>
    ''' </summary>
    ''' <param name="columns"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("Student")
    ''' mySQL.addWhereGT(Student.Columns.Age,10)
    ''' mySQL.addOrder(OrderByDirection.Ascending, Student.Columns.Age)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	29/10/2008	Created
    ''' </history>
    Public Sub addOrder(ByVal direction As OrderByDirection, ByVal ParamArray columns() As [Enum])
        Dim desc As Boolean = True
        If direction = OrderByDirection.Ascending Then
            desc = False
        End If
        doOrderBy(m_primaryTable, desc, columns)
    End Sub




    ''' <overloads>
    ''' Add an ORDER BY (ascending sort) to the SELECT clause.
    ''' </overloads>
    ''' <summary>
    ''' Add an ORDER BY (ascending sort) to the SELECT clause, using the specified columns in the specified table.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="columns"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.addInnerJoin(LunchOrder.Columns.StudentID, New Student, Student.Columns.StudentID)
    ''' mySQL.addWhereGT(New Student, Student.Columns.Age,10)
    ''' mySQL.addOrderBy(New Student, Student.Columns.Age, Student.Columns Name)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addOrderBy(ByVal foreignTable As DataObject, ByVal ParamArray columns() As [Enum])
        doOrderBy(foreignTable.TableName, False, columns)
    End Sub


    ''' <summary>
    ''' Add an ORDER BY DESC (descending sort) to the SELECT clause, using the specified columns in the specified table.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="columns"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.addInnerJoin(LunchOrder.Columns.StudentID, New Student, Student.Columns.StudentID)
    ''' mySQL.addWhereGT(New Student, Student.Columns.Age,10)
    ''' mySQL.addOrderByDesc(New Student, Student.Columns.Age, Student.Columns Name)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public Sub addOrderByDesc(ByVal foreignTable As DataObject, ByVal ParamArray columns() As [Enum])
        doOrderBy(foreignTable.TableName, True, columns)
    End Sub



    ''' <summary>
    ''' Add an ORDER BY (ascending sort) or ORDER BY DESC (descending sort) to the SELECT clause, using the specified columns in the specified table.
    ''' </summary>
    ''' <param name="foreignTable"></param>
    ''' <param name="direction"></param>
    ''' <param name="columns"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <code>
    ''' Dim mySQL as New SQLBuilder("LunchOrder")
    ''' mySQL.addInnerJoin(LunchOrder.Columns.StudentID, New Student, Student.Columns.StudentID)
    ''' mySQL.addWhereGT(New Student, Student.Columns.Age,10)
    ''' mySQL.addOrder(New Student, OrderByDirection.Ascending, Student.Columns.Age, Student.Columns Name)
    ''' </code>
    ''' </example>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>
    Public Sub addOrder(ByVal direction As OrderByDirection, ByVal foreignTable As DataObject, ByVal ParamArray columns() As [Enum])
        Dim desc As Boolean = True
        If direction = OrderByDirection.Ascending Then
            desc = False
        End If
        doOrderBy(foreignTable.TableName, desc, columns)
    End Sub

    Public Sub addOrder(ByVal direction As OrderByDirection, ByVal selection As DOSelection)
        Dim desc As Boolean = True
        If direction = OrderByDirection.Ascending Then
            desc = False
        End If
        doOrderBy(selection.Render(False), desc)
    End Sub

    Private Sub doOrderBy(ByVal tablename As String, ByVal desc As Boolean, ByVal ParamArray columns() As [Enum])
        Dim i As Integer
        For i = 0 To columns.Length - 1
            doOrderBy(tablename & "." & columns(i).ToString(), desc)
        Next
    End Sub

    Private Sub doOrderBy(ByVal selectionName As String, ByVal desc As Boolean)
        If mLOGIC_ORderby = False Then
            mLOGIC_ORderby = True
            mLOGIC_ORderbyStr = "ORDER BY "
        Else
            mLOGIC_ORderbyStr = mLOGIC_ORderbyStr & " , "
        End If
        mLOGIC_ORderbyStr = mLOGIC_ORderbyStr & selectionName & IIf(desc, " DESC", " ASC")
    End Sub


#End Region



#Region "Range"

    ''' <summary>
    ''' Zero-based index of the first row of results to retrieve.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Friend Property StartRowNumber() As Integer
        Get
            Return Me.m_startRowNumber
        End Get
        Set(ByVal value As Integer)
            Me.m_startRowNumber = value
        End Set
    End Property


    ''' <summary>
    ''' Zero-based index of the last row of results to retrieve
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Friend Property EndRowNumber() As Integer
        Get
            Return Me.m_endRowNumber
        End Get
        Set(ByVal value As Integer)
            Me.m_endRowNumber = value
        End Set
    End Property


    ''' <summary>
    ''' Clear the results range so that we retrieve all rows
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub ClearRange()
        Me.m_startRowNumber = -1
        Me.m_endRowNumber = -1
    End Sub

#End Region

    ''' <summary>
    ''' Returns the name of the primary table that the SQLBuilder is currently using.
    ''' </summary>
    ''' <value></value>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[tres]	1/09/2006	Created
    ''' </history>

    Public ReadOnly Property TableName() As String
        Get
            Return m_primaryTable
        End Get
    End Property


    Private Function RenderOperator(ByVal op As DOOperator) As String
        Select Case op
            Case DOOperator.Equal
                Return "="
            Case DOOperator.NotEqual
                Return "<>"
            Case DOOperator.GT
                Return ">"
            Case DOOperator.GTE
                Return ">="
            Case DOOperator.LT
                Return "<"
            Case DOOperator.LTE
                Return "<="
        End Select
    End Function

End Class
